package se.openprocesslogger.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Map;

import org.apache.log4j.Logger;

import se.openprocesslogger.log.LoggingTask;
import se.openprocesslogger.log.Subscription;

public class LogTaskSaver {
	private Logger log = Logger.getLogger(LogTaskSaver.class);
	
	public static OPLDatabase instance(){
		return OPLDatabase.instance();
	}
	private Connection dbConnection;
	
	protected LogTaskSaver(Connection dbConnection){
		this.dbConnection = dbConnection;
	}
	
	protected long addLogTask(LoggingTask task) throws Throwable {
		log.debug("Adding log task " +task.getName());
		storeLogTask(dbConnection, task);
		log.debug("log task added" +task.getName());
		return task.getTaskId();
	}
	
	protected boolean deleteLogTask(LoggingTask task){
		boolean result = DataFetcher.instance().hasData(task.getTaskId());
		if(!result){
			deleteLogTask(dbConnection, task);
			result = true;			
		}
		return result;
	}
	
	private void storeLogTask(Connection con, LoggingTask task) throws Throwable{
		//Check if Task exists.
		PreparedStatement stmt = con.prepareStatement("SELECT * FROM LogTask WHERE name = ?",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		stmt.setString(1,task.getName());
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			task.setTaskId(rs.getLong("id"));
			rs.updateString("name", task.getName());
			rs.updateTimestamp("startTime", new Timestamp(task.getStartTime()));
			rs.updateTimestamp("stopTime", new Timestamp(task.getStopTime()));
			if(task.getProperty("Info")!=null)
				rs.updateString(3, task.getProperty("Info"));
			rs.updateRow();
			rs.close();
			stmt.close();
			updateLogTaskProperties(con, task);
			updateLogTaskSubscriptions(con, task);
			return;
		}
		rs.close();
		stmt.close();
		//If not then insert it.
		stmt = con.prepareStatement("INSERT INTO LogTask (name,info, startTime, stopTime) VALUES(?,?,?,?)");
		stmt.setString(1, task.getName());
		stmt.setString(2,task.getProperty("Info"));
		stmt.setTimestamp(3, new Timestamp(task.getStartTime()));
		stmt.setTimestamp(4, new Timestamp(task.getStopTime()));
		stmt.execute();
		stmt.close();

		//Then get id.
		stmt = con.prepareStatement("SELECT id FROM LogTask WHERE name = ?",ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		stmt.setString(1,task.getName());
		rs = stmt.executeQuery();
		if(rs.next()){
			task.setTaskId(rs.getLong(1));
		}
		rs.close();
		stmt.close();

		//Then insert all the properties given to it.
		stmt = con.prepareStatement("INSERT INTO LogTaskProperties (name,value,object) VALUES(?,?,?)");
		for(Map.Entry<String,String> prop :task.getProperties().entrySet()){
			stmt.setString(1, prop.getKey());
			stmt.setString(2,prop.getValue());
			stmt.setLong(3,task.getTaskId());
			stmt.addBatch();
		}
		stmt.executeBatch();
		stmt.close();
		
		// Save subscriptions
		stmt = con.prepareStatement("INSERT INTO Subscriptions (name,logtask) VALUES(?,?)");
		for(Subscription sub : task.getSubscriptions().values()){
			stmt.setString(1, sub.getValueAddress());
			stmt.setLong(2, task.getTaskId());
			stmt.addBatch();
		}
		int[] a = stmt.executeBatch();
		stmt.close();
		log.debug("Updated log task with "+a.length +" subscriptions");
		
		// Create table for data
		String tableName2 = "LogTaskData"+task.getTaskId();
		stmt = con.prepareStatement(new StringBuilder().append("CREATE TABLE ").append(tableName2)
				.append(" (id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY CONSTRAINT ").append(tableName2)
				.append("_PK PRIMARY KEY, name VARCHAR(500), value BLOB, ")
				.append(" batchsize INT NOT NULL, ")
				.append("fromts TIMESTAMP NOT NULL, tots TIMESTAMP NOT NULL)").toString());
		stmt.execute();
		stmt.close();
	}
		
	private void deleteLogTask(Connection con, LoggingTask task){
		try {
			// Delete subscriptions
			PreparedStatement stmt = con.prepareStatement("DELETE FROM Subscriptions WHERE logtask=?");
			stmt.setLong(1, task.getTaskId());
			stmt.execute();
			stmt.close();
			
			// Delete properties
			stmt = con.prepareStatement("DELETE FROM LogTaskProperties WHERE object=?");
			stmt.setLong(1, task.getTaskId());
			stmt.execute();
			stmt.close();
			
			// Delete from tasks table
			stmt = con.prepareStatement("DELETE FROM LogTask WHERE id=?");
			stmt.setLong(1,task.getTaskId());
			stmt.execute();
			stmt.close();
			
			// Delete table for processed data
			stmt = con.prepareStatement("DROP TABLE LogTaskData"+task.getTaskId());
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			log.error("Error deleting log task: "+e.getMessage());
			e.printStackTrace();
		}
	}
	
	private void updateLogTaskSubscriptions(Connection con, LoggingTask task) throws Throwable{
		ArrayList<String> insert = new ArrayList<String>();
		for(Subscription sub : task.getSubscriptions().values()){
			try {
				PreparedStatement s = con.prepareStatement("SELECT * FROM Subscriptions WHERE name=? AND logtask=?");
				s.setString(1, sub.getValueAddress());
				s.setLong(2, task.getTaskId());
				ResultSet rs = s.executeQuery();
				if(!rs.next()){ // Does not exist
					insert.add(sub.getValueAddress());
				}
				s.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				log.error(e.getMessage());				
			}
		}
		PreparedStatement batch = con.prepareStatement("INSERT INTO Subscriptions (name,logtask) VALUES(?,?)");
		for(String sub : insert){
			batch.setString(1, sub);
			batch.setLong(2, task.getTaskId());
			batch.addBatch();
		}
		int[] a = batch.executeBatch();
		batch.close();
		if(a.length>0)
			log.debug("Updated log task with "+a.length +" subscriptions");
	}

	private void updateLogTaskProperties(Connection dbconn, LoggingTask task) throws Throwable{
		for(Map.Entry<String,String> prop : task.getProperties().entrySet()){
			PreparedStatement stmt = dbconn.prepareStatement("SELECT value FROM LogTaskProperties WHERE object=? AND name=?",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			stmt.setLong(1,task.getTaskId());
			stmt.setString(2,prop.getKey());
			ResultSet rs = stmt.executeQuery();
			if(rs.next()){
				rs.updateString(1,prop.getValue());
				rs.updateRow();
				rs.close();
				stmt.close();
			}else{
				rs.close();
				stmt.close();
				stmt=dbconn.prepareStatement("INSERT INTO LogTaskProperties (name,value,object) VALUES(?,?,?)");
				stmt.setString(1,prop.getKey());
				stmt.setString(2,prop.getValue());
				stmt.setLong(3,task.getTaskId());
				stmt.execute();
				stmt.close();
			}			
		}
	}
	/*
	private String getTimeString(long ms){
		SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss");
		return sdf.format(new Date(ms));
	}*/
	
}
